-- 示例1：创建表空间
CREATE TABLESPACE worktbs
DATAFILE 'D:\ORADATA\APTECH\WORKTBS012.DBF' SIZE 10M AUTOEXTEND ON;

-- 查看表空间
SELECT file_name,tablespace_name,bytes,autoextensible
FROM dba_data_files
WHERE tablespace_name = 'worktbs';



-- 示例2：创建用户
CREATE USER martin
IDENTIFIED BY martinpwd
DEFAULT TABLESPACE worktbs
TEMPORARY TABLESPACE temp;

-- 示例3：修改口令
ALTER USER martin
IDENTIFIED BY mpwd;

-- 删除用户
DROP USER martin CASCADE;

-- 示例4：赋予权限
GRANT connect,resource TO martin;		-- 授予CONNECT 和 RESOURCE 两个角色
REVOKE connect,resource FROM martin;	-- 撤销 CONNECT 和 RESOURCE 两个角色
GRANT SELECT ON SCOTT.emp TO martin;	-- 允许用户查看 emp 表中的记录
GRANT UPDATE ON SCOTT.emp TO martin;	-- 允许用户更新 emp 表中的记录

-- 示例5：创建序列
CREATE SEQUENCE seq1
	START WITH 10
	INCREMENT BY 1
	MAXVALUE 2000
	NOCYCLE
	CACHE 30;

-- 示例6
INSERT INTO toys(toyid,toyname,toyprice) VALUES (seq1.NEXTVAL,'TWENTY',25);
INSERT INTO toys(toyid,toyname,toyprice) VALUES (seq1.NEXTVAL,'MAGIC PENCTL',75);
-- 查询数据
SELECT * FROM toys;


-- 示例7
-- 使用SYSTEM用户登录，授予A_oe用户获得访问A_hr模式下的employee表的权限
GRANT SELECT ON A_hr.employee TO A_oe;
GRANT CREATE SYNONYM TO A_oe;

-- 连接A_oe用户
-- 创建同义词SY_EMP
CREATE SYNONYM SY_EMP FOR A_hr.employee;
-- 访问同义词
SELECT * FROM SY_EMP;

-- 使用SYSTEM用户登录，A_hr获得创建公有同义词权限
GRANT CREATE PUBLIC SYNONYM TO A_hr;

-- 在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名
CREATE PUBLIC SYNONYM public_sy_emp FOR employee;
-- 在A_oe模式下访问公有同义词
SELECT * FROM public_sy_emp;

-- 删除私有同义词
DROP SYNONYM A_oe.sy_emp;
-- 删除公有同义词
DROP PUBLIC SYNONYM A_hr.public_Sy_emp;

-- 示例10：创建唯一索引
CREATE UNIQUE INDEX index_unique_grade ON salgrade(grade);

-- 示例11：创建方向键索引
CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;

-- 示例12：创建位图索引
CREATE BITMAP INDEX index_bit_job ON employee(job);

-- 示例13：创建大写函数索引
CREATE INDEX index_ename ON employee(UPPER(ename));
-- 删除位图索引
DROP INDEX index_bit_job;

-- 示例14：范围分区
CREATE TABLE SALES1
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL,
	 SALES_COST NUMBER(10) NOT NULL,
	 AREACODE VARCHAR2(5) NOT NULL
)
PARTITION BY RANGE (SALES_DATE)
(
   PARTITION P1 VALUES LESS THAN (to_date('2013-04-1', 'yyyy-mm-dd')),
   PARTITION P2 VALUES LESS THAN (to_date('2013-07-1', 'yyyy-mm-dd')),
   PARTITION P3 VALUES LESS THAN (to_date('2013-10-1', 'yyyy-mm-dd')),
   PARTITION P4 VALUES LESS THAN (to_date('2014-01-1', 'yyyy-mm-dd')),
   PARTITION P5 VALUES LESS THAN (maxvalue)
);

-- 查看第三季度的数据
SELECT * FROM sales1 PARTITION(P3);

-- 删除第三季度的数据
DELETE FROM sales1 PARTITION(P3);


-- 示例15：间隔分区
-- 创建间隔分区表
CREATE TABLE SALES2
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL
)
    PARTITION BY RANGE(SALES_DATE)
    INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
    (PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
-- 插入数据
  INSERT INTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');
-- 获得分区情况
  SELECT table_name,partition_name 
       FROM user_tab_partitions 
   WHERE table_name=UPPER('sales2');

-- 查询分区数据
	SELECT * FROM sales2 PARTITION(sys_P82);
